﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Security;
using System.Security.Cryptography;
using System.Text;
using System.IO;
using System.Text.RegularExpressions;

namespace Dx.SqlAccess
{
    public abstract class SqlDataAccess
    {
        private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
        public static readonly string SQLConnString1 = DBSecurity.Decrypt(System.Configuration.ConfigurationManager.AppSettings["SQLConnString1"].ToString());
        public static readonly string SQLConnString2 = DBSecurity.Decrypt(System.Configuration.ConfigurationManager.AppSettings["SQLConnString2"].ToString());
        public static readonly string SQLConnString3 = DBSecurity.Decrypt(System.Configuration.ConfigurationManager.AppSettings["SQLConnString3"].ToString());

        public static string SQLConnString4()
        {
            string temp = System.Configuration.ConfigurationManager.AppSettings["SQLConnString4"];
            if (!string.IsNullOrEmpty(temp))
            {
                return DBSecurity.Decrypt(temp);
            }
            else
            {
                return "";
            }
        }
        //设置输出参数，用于将存储过程执行结果返回
        public static string outTest = "";

        public static void CacheParameters(string cacheKey, params SqlParameter[] cmdParms)
        {
            parmCache[cacheKey] = cmdParms;
        }

        public static void DataSetXmlDocument(string strPathXml, string connString, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            XmlReader reader = null;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                try
                {
                    try
                    {
                        //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                        AddLog(connection, null, "DataSetXmlDocument1", cmdType, cmdText, cmdParms);
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                        XmlDocument document = new XmlDocument();
                        XmlElement newChild = document.CreateElement("ROOT");
                        document.DocumentElement.AppendChild(newChild);
                        reader = cmd.ExecuteXmlReader();
                        while (reader.EOF)
                        {
                            newChild.AppendChild(document.ReadNode(reader));
                        }
                        document.Save(strPathXml);
                    }
                    catch
                    {
                        throw;
                    }
                }
                finally
                {
                    reader.Close();
                    connection.Close();
                }
            }
        }

        public static int ExecuteNonQuery(SqlConnection conn, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(conn, null, "ExecuteNonQuery2", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static string ExecuteNonQuery_WithErrorMessage(SqlConnection conn, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(conn, null, "ExecuteNonQuery2", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            conn.FireInfoMessageEventOnUserErrors = true;
            conn.StatisticsEnabled = true;
            conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return outTest;
        }

        protected static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e)
        {
            outTest = "";
            foreach (SqlError sr in e.Errors)
            {
                outTest += sr.Message;
                outTest += ",";
            }
            if (outTest.Length > 0)
            {
                outTest = outTest.Remove(outTest.Length - 1, 1);
            }
        }

        public static int ExecuteNonQueryWx(SqlConnection conn, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(conn, null, "ExecuteNonQueryWx3", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static int ExecuteNonQueryWx(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(trans.Connection, trans, "ExecuteNonQueryWx3", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(trans.Connection, trans, "ExecuteNonQuery4", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(trans.Connection, trans, "ExecuteNonQuery5", cmdType, cmdText, cmdParms);
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            int num = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return num;
        }

        public static SqlDataReader ExecuteReader(SqlConnection conn, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            SqlDataReader reader2;
            SqlCommand cmd = new SqlCommand();
            try
            {
                //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                AddLog(conn, null, "ExecuteReader6", cmdType, cmdText, cmdParms);
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch
            {
                conn.Close();
                throw;
            }
            return reader2;
        }

        public static object ExecuteScalar(SqlConnection conn, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            object obj3;
            try
            {
                SqlCommand cmd = new SqlCommand();
                //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                AddLog(conn, null, "ExecuteScalar7", cmdType, cmdText, cmdParms);
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object obj2 = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                obj3 = obj2;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
            return obj3;
        }

        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            object obj3;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                try
                {
                    //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                    AddLog(connection, null, "ExecuteScalar8", cmdType, cmdText, cmdParms);
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                    object obj2 = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    obj3 = obj2;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return obj3;
        }

        public static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] parameterArray = (SqlParameter[]) parmCache[cacheKey];
            if (parameterArray != null)
            {
                SqlParameter[] parameterArray2 = new SqlParameter[parameterArray.Length];
                int index = 0;
                int length = parameterArray.Length;
                while (index < length)
                {
                    parameterArray2[index] = (SqlParameter) ((ICloneable) parameterArray[index]).Clone();
                    index++;
                }
                if (parameterArray2.Length > 0)
                {
                    return parameterArray2;
                }
            }
            return null;
        }

        public static DataSet GetDataSetEx(string connString, CommandType cmdType, string cmdText, string tableName, params SqlParameter[] cmdParms)
        {

            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(null, null, "GetDataSet900", cmdType, cmdText, cmdParms);

            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(connString);

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                SqlDataAdapter DataAdapter = new SqlDataAdapter();
                DataAdapter.SelectCommand = cmd;
                DataSet ds = new DataSet();
                DataAdapter.Fill(ds, tableName);
                cmd.Parameters.Clear();
                return ds;
            }
            catch
            {
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataSet GetDataSet(string connString, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            DataSet set2;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                try
                {
                    //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                    AddLog(connection, null, "GetDataSet9", cmdType, cmdText, cmdParms);
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    cmd.Parameters.Clear();
                    set2 = dataSet;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return set2;
        }

        public static DataSet GetDataSet(SqlTransaction trans, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {
            //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
            AddLog(null, trans, "GetDataSet100", cmdType, cmdText, cmdParms);
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
            SqlDataAdapter DataAdapter = new SqlDataAdapter();
            DataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            DataAdapter.Fill(ds);
            cmd.Parameters.Clear();
            return ds;
        }

        public static DataSet GetDataSet(string connString, CommandType cmdType, string cmdText, string tableName, ParameterCollection cmdParms)
        {
            DataSet set2;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                try
                {
                    //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                    AddLog(connection, null, "GetDataSet10", cmdType, cmdText, cmdParms);
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, tableName);
                    cmd.Parameters.Clear();
                    set2 = dataSet;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return set2;
        }

        public static DataView GetDataView(string connString, CommandType cmdType, string cmdText, string tableName, ParameterCollection cmdParms)
        {
            DataView view2;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection connection = new SqlConnection(connString))
            {
                try
                {
                    //任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请
                    AddLog(connection, null, "GetDataView11", cmdType, cmdText, cmdParms);
                    PrepareCommand(cmd, connection, null, cmdType, cmdText, cmdParms);
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet, tableName);
                    DataView defaultView = new DataView();
                    defaultView = dataSet.Tables[0].DefaultView;
                    cmd.Parameters.Clear();
                    view2 = defaultView;
                }
                catch
                {
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
            return view2;
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, ParameterCollection commandParameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = cmdType;
            //cmd.CommandTimeout = 90; 
            if ((commandParameters != null) && (commandParameters.Count > 0))
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    if (commandParameters[i].ParameterName.IndexOf("_SET_COMMAND_OUTTIME") >= 0)
                    {
                        int CommandTimeout = 30;
                        if (int.TryParse(commandParameters[i].Value.ToString(), out CommandTimeout))
                            cmd.CommandTimeout = CommandTimeout;
                        continue;
                    }

                    commandParameters[i].InitRealParameter();
                    cmd.Parameters.Add(commandParameters[i].RealParameter as SqlParameter);
                }
            }
        }

        #region 为执行命令准备参数(PrepareCommand)
        /// <summary>
        /// 为执行准备命令(Prepare a command for execution)
        /// </summary>
        /// <param name="cmd">要执行的SQLcommand命令</param>
        /// <param name="conn">数据库连接串</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdType">命令类型(stored procedure, text, etc.)</param>
        /// <param name="cmdText">存储过程名或SQL语句</param>
        /// <param name="cmdParms">数据参数</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            // 打开数据库连接



            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            // 是否是事务



            if (trans != null)
                cmd.Transaction = trans;

            // 命令类型(stored procedure, text, etc.)
            cmd.CommandType = cmdType;
            //cmd.CommandTimeout = 90; 
            // 数据参数不为空，添加每个参数到cmd中



            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                {
                    if (parm.ParameterName.IndexOf("_SET_COMMAND_OUTTIME") >= 0)
                    {
                        int CommandTimeout = 30;
                        if (int.TryParse(parm.Value.ToString(), out CommandTimeout))
                            cmd.CommandTimeout = CommandTimeout;
                        continue;
                    }

                    cmd.Parameters.Add(parm);
                }
            }
        }
        
        #endregion

        #region 任何人不允许注释AddLog方法和调用AddLog的地方,需要修改时要先跟部门经理申请 wx
        public static void AddLog(SqlConnection conn, SqlTransaction trans, string method, CommandType cmdType, string cmdText, ParameterCollection cmdParms)
        {

            #region 检查一下sql语句
            string notallow = "";
            if (!cmdType.Equals(CommandType.StoredProcedure) && CheckSQLText(cmdText, out notallow) <= 0) //语句中有不允许的内容，如果是开发人员写的，请改写sql语句或写存储过程实现
            {
                if (string.IsNullOrEmpty(notallow) == false)
                {
                    DBSaveLog.WriteErrLog("sql语句中不允许出现：" + notallow);
                }

                throw new Exception("数据格式不正确！！！");
            }
            #endregion



            #region 检查sql语句中的参数值

            bool OK = true;
            string sql = "INSERT INTO OperatorLog " +
                         " (OperLogId " +
                         " ,UserId " +
                         " ,UserName " +
                         " ,UserType " +
                         " ,ModuleName " +
                         " ,Url " +
                         " ,Content " +
                         " ,OperDate " +
                         " ,LocalIp) " +
                         " VALUES " +
                         " ('sql' " +
                         " ,'sql' " +
                         " ,'sql' " +
                         " ,'sql' " +
                         " ,@method " +
                         " ,'' " +
                         " ,@Content " +
                         " ,getdate() " +
                         " ,'')";

            string strParam = string.Empty;
            if (cmdParms != null)
                for (int i = 0; i < cmdParms.Count; i++)
                {
                    QueryParameter qp = cmdParms[i];
                    if (qp != null)
                        strParam += string.Format(" | ParamName:{1} | Value:{2} | Type:{0} |", qp.DbType, qp.ParameterName, qp.Value);

                    if (qp.Direction == ParameterDirection.Output || qp.Value == null) continue;
                    //if (CheckSQLImmit(qp.ParameterName) || CheckSQLImmit(qp.Value.ToString()))
                    if (CheckSQLImmit(qp.Value.ToString())) //把参数名去掉了，参数名不用检查
                    {
                        OK = false;
                        break;
                    }
                }
            if (!OK)
            {
                ParameterCollection param = new ParameterCollection();
                param.Add("@method", method);
                param.Add("@Content", cmdText + " " + strParam);

                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, conn, trans, CommandType.Text, sql, param);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                throw new Exception("数据格式不正确！");
            }
            #endregion
        }

        public static void AddLog(SqlConnection conn, SqlTransaction trans, string method, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {


            #region 检查一下sql语句
            string notallow = "";
            if (!cmdType.Equals(CommandType.StoredProcedure) && CheckSQLText(cmdText, out notallow) <= 0) //语句中有不允许的内容，如果是开发人员写的，请改写sql语句或写存储过程实现
            {
                if (string.IsNullOrEmpty(notallow) == false)
                {
                    DBSaveLog.WriteErrLog("sql语句中不允许出现：" + notallow);
                }

                throw new Exception("数据格式不正确！！！");
            }
            #endregion

            #region 检查sql语句中的参数值
            bool OK = true;
            string sql = "INSERT INTO OperatorLog " +
                         " ( " +
                         " UserId " +
                         " ,UserName " +
                         " ,UserType " +
                         " ,ModuleName " +
                         " ,Url " +
                         " ,Content " +
                         " ,OperDate " +
                         " ,LocalIp) " +
                         " VALUES " +
                         " ( " +
                         " 'sql' " +
                         " ,'sql' " +
                         " ,'sql' " +
                         " ,@method " +
                         " ,'' " +
                         " ,@Content " +
                         " ,getdate() " +
                         " ,'')";

            string strParam = string.Empty;
            if (cmdParms != null)
                for (int i = 0; i < cmdParms.Length; i++)
                {
                    SqlParameter qp = cmdParms[i];
                    if (qp != null)
                        strParam += string.Format(" | ParamName:{1} | Value:{2} | Type:{0} |", qp.DbType, qp.ParameterName, qp.Value);

                    if (qp.Direction == ParameterDirection.Output || qp.Value == null) continue;
                    //if (CheckSQLImmit(qp.ParameterName) || CheckSQLImmit(qp.Value.ToString()))
                    if (CheckSQLImmit(qp.Value.ToString())) //把参数名去掉了，参数名不用检查
                    {
                        OK = false;
                        break;
                    }
                }

            if (!OK)
            {
                //try
                //{
                    ParameterCollection param = new ParameterCollection();
                    param.Add("@method", method);
                    param.Add("@Content", cmdText + " " + strParam);

                    SqlCommand cmd = new SqlCommand();
                    PrepareCommand(cmd, conn, trans, CommandType.Text, sql, param);
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();

                    DBSaveLog.WriteErrLog("输入的参数中有攻击内容1：" + cmdText + ";" + strParam);

                    throw new Exception("数据格式不正确！");
                //}
                //catch
                //{
                //    //赵灵军 增加抛弃异常 原因：后台即时充值页面中商品类别管理的内容，老是提示截取二进制字符串 2010-12-07
                    
                //}
            }
            #endregion
        }


        #region 检查sql语句,任何人不允许,需要修改时要先跟部门经理申请 wx
        public static int CheckSQLText(string sql,out string notallowstr)
        {
            notallowstr = "";

            if (string.IsNullOrEmpty(sql))
            {
                return 1;
            }

            sql = sql.ToLower();


            //关键字要全字匹配
            if (KeyIsMatchForSQL(sql, out notallowstr) < 0)
            {
                return -1;
            }

            //任何人不允许删除关键字，如需修改，先跟部门经理申请  
            //下面这些模糊匹配
            //sp_  这里的筛选删掉这个关键字了（团卡用）
            //SELECT gamePicUrl,isnull(game_url,'') as  game_url,isnull(game_sp_url,'') as game_sp_url,GameName FROM ka_SetGame where BossPid=@BossPid  order by sort asc
            string keyword = "%20,--,proc,xp_,hkey_,char(0x,selects,fromf,wherew,deleted,updateu,inserti";

            string[] keywords = keyword.Split(',');

            if (keywords != null && keywords.Length > 0)
            {
                for (int i = 0; i < keywords.Length - 1; i++)
                {
                    if (sql.IndexOf(keywords[i]) >= 0) //说明sql不合法
                    {
                        notallowstr = keywords[i];

                        DBSaveLog.WriteErrLog("输入的参数中有攻击内容2：" + notallowstr);

                        return -1;
                    }
                }
            }

            return 1;
        }
        #endregion



        public static int KeyIsMatchForSQL(string str, out string notallowstr)
        {            
            int rtn = 1;
            notallowstr = "";

            if (string.IsNullOrEmpty(str))
            {
                return 1;  //空串不用查
            }

            //任何人不允许删除关键字，如需修改，先跟部门经理申请 
            string key = "create|declare|exec|execute|sysdatabases|database|dbid|db_name|user_name|shutdown|drop|truncate|master|cmdshell|col_name|syscolumns|xtype|object_id|sysobjects";

            string[] keys = key.Split('|');

            if (keys != null && keys.Length > 0)
            {
                for (int i = 0; i < keys.Length - 1; i++)
                {
                    int pos = str.IndexOf(keys[i]);
                    if (pos >= 0) //存在
                    {
                        //判断是否全部匹配
                        int len = keys[i].Length;

                        if (str.Length >= pos + len + 1)
                        {
                            string nextchar = str.Substring(pos + len, 1);
                            if (nextchar == " ")
                            {
                                notallowstr = keys[i];

                                DBSaveLog.WriteErrLog("输入的参数中有攻击内容3：" + notallowstr);

                                return -1;
                                break;
                            }
                        }
                        else
                        {
                            notallowstr = keys[i];

                            DBSaveLog.WriteErrLog("输入的参数中有攻击内容4：" + notallowstr);

                            return -1;
                            break;
                        }
                    }
                }
            }
            else
            {
                return 1;
            }


            return rtn;

        }



        public static int KeyIsMatchForParam(string str, out string notallowstr)
        {
            int rtn = 1;
            notallowstr = "";


            if (string.IsNullOrEmpty(str)) 
            {
                return 1;  //空串不用查
            }
            

            //任何人不允许删除关键字，如需修改，先跟部门经理申请 
            string key = "cmdshell|sp_executesql|drop|table|insert|select|from|join|delete|update|create|declare|exec|execute|sysdatabases|database|dbid|db_name|user_name|shutdown|drop|truncate|master|cmdshell|col_name|syscolumns|xtype|object_id|sysobjects";

            string[] keys = key.Split('|');

            if (keys != null && keys.Length > 0)
            {
                for (int i = 0; i < keys.Length - 1; i++)
                {
                    int pos = str.IndexOf(keys[i]);
                    if (pos >= 0) //存在
                    {
                        //判断是否全部匹配
                        int len = keys[i].Length;

                        if (str.Length >= pos + len + 1)
                        {
                            string nextchar = str.Substring(pos + len, 1);
                            if (nextchar == " ")
                            {
                                notallowstr = keys[i];

                                DBSaveLog.WriteErrLog("输入的参数中有攻击内容5：" + notallowstr);

                                return -1;
                                break;
                            }
                        }
                        else
                        {
                            notallowstr = keys[i];

                            DBSaveLog.WriteErrLog("输入的参数中有攻击内容6：" + notallowstr);

                            return -1;
                            break;
                        }
                    }
                }
            }
            else
            {
                return 1;
            }


            if (str.IndexOf("char(0x") >= 0)
            {
                notallowstr = "char(0x";

                DBSaveLog.WriteErrLog("输入的参数中有攻击内容7：" + notallowstr);
                return -1;
            }


            return rtn;

        }


        #endregion


        public static bool CheckSQLImmit(string checkText)
        {
            string tempString = checkText.ToLower();
            //if (NeedUrlCodeCheck)
            //tempString = HttpUtility.UrlDecode(tempString);
            //说明：不能存在'+空格的存在，并且，不能有空格+字符+空格的命令存在

            #region 确定是时间类型的数值，则不用匹配
            DateTime dt = new DateTime();

            bool check = DateTime.TryParse(checkText, out dt);

            if (check)
                return false;
            #endregion


            //string sqlString = @"\s*\'\s+|\s(and|exec|insert|select|delete|update|count|\*|\%|chr|mid|master|truncate|char|declare)\s";

            //任何人不允许删除关键字，如需修改，先跟部门经理申请  
            string sqlString = @"\s(declare|sp_|xp_|hkey_|database|shutdown|dbid|db_name|sysobjects|object_id|syscolumns|xtype|create|exec|insert|select|from|join|delete|update|master|truncate|declare|sp_executesql|drop|table|col_name|char\(0x|cmdshell)\s";

            Regex reg = new Regex(sqlString);
            //return reg.IsMatch(tempString);
            bool bRet = reg.IsMatch(tempString);
            if (bRet)
            {
                bool aa = false;
            }



            if (bRet == false)
            {
                string notallowstr = "";
                if (KeyIsMatchForParam(tempString, out notallowstr) < 0)
                {
                    bRet = true;
                }
            }


            return bRet;
        }

        public static ParameterCollection Hash2Param(Hashtable values)
        {
            if (values == null) return null;
            ParameterCollection result = new ParameterCollection(values.Count);
            foreach (DictionaryEntry de in values)
            {
                string name = string.Empty;
                if (de.Key.ToString().IndexOf("@") == -1)
                    name = "@" + de.Key.ToString();
                else
                    name = de.Key.ToString();
                result.Add(name, de.Value);
            }
            return result;
        }

        #region 提交修改后的DataTable。



        public static void ApplyData(string tablename, DataTable change)
        {
            string sql = "select * from " + tablename + " where 1 = 2";

            SqlConnection conn = new SqlConnection(SQLConnString1);

            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, conn);
            SqlCommandBuilder builder = new SqlCommandBuilder(dataAdapter);
            dataAdapter.UpdateCommand = builder.GetUpdateCommand();
            dataAdapter.InsertCommand = builder.GetInsertCommand();
            dataAdapter.DeleteCommand = builder.GetDeleteCommand();


            if (conn.State != ConnectionState.Open)
                conn.Open();

            try
            {
                dataAdapter.Update(change);
            }
            catch (Exception e)
            {
                //SaveLog.WriteLog(e.Message);
                throw new Exception(e.Message);
            }
            finally
            {
                conn.Close();
            }
        }
        #endregion

        #region 获取表的结构
        public static DataColumnCollection GetDataTableSchema(string TableName)
        {
            DataTable dt = GetNullDataTable(TableName);
            if (dt != null)
                return dt.Columns;
            else
                return null;
        }
        public static DataTable GetNullDataTable(string TableName)
        {
            ParameterCollection param = new ParameterCollection();
            param.Add("@TableName", TableName);
            string ls_sql = "select * from @TableName where 1 = 2";
            DataSet ds = SqlDataAccess.GetDataSet(SqlDataAccess.SQLConnString1, CommandType.Text, ls_sql, "temp", param);
            if (ds != null && ds.Tables.Count == 1)
                return ds.Tables[0];
            else
                return null;
        }
        #endregion

        //public bool IsAddLog

        //public bool CheckParam(SqlConnection conn, SqlTransaction trans, string method, string cmdText, ParameterCollection cmdParms)
        //{

        //}
    }

    public class DBSecurity
    {
        private const string sKey = "23,24,82,41,40,28,53,34";
        private const string sIV = "21,49,73,15,86,15,24,14";
        # region 加密解密
        //方法 
        //加密方法 
        public static string Encrypt(string pToEncrypt)
        {
            try
            {
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();
                //把字符串放到byte数组中 			
                byte[] inputByteArray = Encoding.Default.GetBytes(pToEncrypt);

                //建立加密对象的密钥和偏移量



                byte[] b_key = new byte[8];
                string[] s_keys = new string[8];
                s_keys = sKey.Split(',');


                for (int i = 0; i <= 7; i++)
                {
                    b_key[i] = Convert.ToByte(s_keys[i].ToString());
                }

                des.Key = b_key;

                byte[] b_iv = new byte[8];
                string[] s_ivs = new string[8];
                s_ivs = sIV.Split(',');


                for (int i = 0; i <= 7; i++)
                {
                    b_iv[i] = Convert.ToByte(s_ivs[i].ToString());
                }

                des.IV = b_iv;


                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);
                //Write the byte array into the crypto stream 
                //(It will end up in the memory stream) 
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();
                //Get the data back from the memory stream, and into a string 
                StringBuilder ret = new StringBuilder();
                foreach (byte b in ms.ToArray())
                {
                    //Format as hex 
                    ret.AppendFormat("{0:X2}", b);
                }
                ret.ToString();
                return ret.ToString();
            }
            catch
            {
                return "";
            }

        }


        //解密方法 
        public static string Decrypt(string pToDecrypt)
        {
            try
            {
                DESCryptoServiceProvider des = new DESCryptoServiceProvider();

                //Put the input string into the byte array 
                byte[] inputByteArray = new byte[pToDecrypt.Length / 2];
                for (int x = 0; x < pToDecrypt.Length / 2; x++)
                {
                    int i = (Convert.ToInt32(pToDecrypt.Substring(x * 2, 2), 16));
                    inputByteArray[x] = (byte)i;
                }

                //建立加密对象的密钥和偏移量，此值重要，不能修改 
                byte[] b_key = new byte[8];
                string[] s_keys = new string[8];
                s_keys = sKey.Split(',');


                for (int i = 0; i <= 7; i++)
                {
                    b_key[i] = Convert.ToByte(s_keys[i].ToString());
                }

                des.Key = b_key;

                byte[] b_iv = new byte[8];
                string[] s_ivs = new string[8];
                s_ivs = sIV.Split(',');


                for (int i = 0; i <= 7; i++)
                {
                    b_iv[i] = Convert.ToByte(s_ivs[i].ToString());
                }

                des.IV = b_iv;


                MemoryStream ms = new MemoryStream();
                CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
                //Flush the data through the crypto stream into the memory stream 
                cs.Write(inputByteArray, 0, inputByteArray.Length);
                cs.FlushFinalBlock();

                //Get the decrypted data back from the memory stream 
                //建立StringBuild对象，CreateDecrypt使用的是流对象，必须把解密后的文本变成流对象 
                StringBuilder ret = new StringBuilder();

                return System.Text.Encoding.Default.GetString(ms.ToArray());
            }
            catch (Exception exp)
            {
                string s = exp.Message.ToString();
                return "";
            }
        }


        #endregion
    }





    public class DBSaveLog
    {

        public string ErrFileDir = string.Empty;

        public static string errpath = System.Configuration.ConfigurationManager.AppSettings["ErrorLogPath"].ToString();

        public DBSaveLog()
        {
            if (System.Configuration.ConfigurationManager.AppSettings["ErrorLogPath"] != null)
                ErrFileDir = System.Configuration.ConfigurationManager.AppSettings["ErrorLogPath"];
        }

        #region 直储充值结果日志 保存方式 html
        public bool WriteLog(string name, string strbody)
        {
            strbody = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "  " + strbody.Replace("\r\n", "<br>") + "<br>";
            try
            {
                if (string.IsNullOrEmpty(errpath)) return false;
                string mm = System.DateTime.Now.ToString(@"yyyyMM");
                string date = System.DateTime.Now.ToString(@"yyyyMMdd");
                date = mm + @"\" + date;
                string fileDir = errpath.TrimEnd('\\') + @"\" + date + @"\";
                string filepath = fileDir;// +name + ".htm";

                name = name + ".htm";

                return WriteLog(filepath, name, strbody);

            }
            catch
            {
                return false;
            }
        }

        public static void WriteErrLog(string strbody)
        {
            strbody = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "  " + strbody.Replace("\r\n", "<br>") + "<br>";


            try
            {
                if (string.IsNullOrEmpty(errpath)) return;
                string mm = System.DateTime.Now.ToString(@"yyyyMM");
                string date = System.DateTime.Now.ToString(@"yyyyMMdd");
                date = mm + @"\" + date;
                string fileDir = errpath.TrimEnd('\\') + @"\" + date + @"\";
                string filepath = fileDir;// +name + ".htm";

                string name = "EsalesError.htm";

                WriteErrLog(filepath, name, strbody);

            }
            catch
            {

            }
        }


        public static void WriteErrLog(string filepath, string name, string strbody)
        {

            StreamWriter my_writer = null;

            try
            {
                if (Directory.Exists(filepath) == false)
                {
                    Directory.CreateDirectory(filepath);
                }
                //如果文件存在，则自动追加方式写
                my_writer = new StreamWriter(filepath + name, true, System.Text.Encoding.Default);
                my_writer.Write(strbody);
                my_writer.Flush();

            }
            catch
            {

            }
            finally
            {
                if (my_writer != null)
                    my_writer.Close();
            }
        }

        /// <summary>
        /// 普通错误日志 按日自动创建文件夹，日志文件名为 Error.htm
        /// </summary>
        /// <param name="strbody">日志详细内容</param>
        /// <returns></returns>
        public bool WriteLog(string strbody)
        {
            strbody = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff") + "  " + strbody.Replace("\r\n", "<br>") + "<br>";
            return WriteLog("EsalesError", strbody);
        }

        public static bool WriteFormatLog1(string filename, string strbody, params object[] arg)
        {
            string error = string.Format(strbody, arg);
            return new DBSaveLog().WriteLog(filename, error);
        }

        public static bool WriteFormatLog(string strbody, params object[] arg)
        {
            string error = string.Format(strbody, arg);
            return new DBSaveLog().WriteLog(error);
        }

        public static bool Debug(string strbody, params object[] arg)
        {
            bool result = true;
            string debug = string.Empty;

            try
            {
                debug = System.Configuration.ConfigurationManager.AppSettings["Debug"];
                if (!string.IsNullOrEmpty(debug) && debug.ToLower() == "true")
                {
                    string error = string.Format(strbody, arg);
                    result = new DBSaveLog().WriteLog(error);
                }
                else
                    result = false;
            }
            catch { }

            return result;
        }

        public static bool Debug1(string filename, string strbody, params object[] arg)
        {
            bool result = true;
            string debug = string.Empty;

            try
            {
                debug = System.Configuration.ConfigurationManager.AppSettings["Debug"];
                if (!string.IsNullOrEmpty(debug) && debug.ToLower() == "true")
                {
                    string error = string.Format(strbody, arg);
                    result = new DBSaveLog().WriteLog(filename, error);
                }
                else
                    result = false;
            }
            catch { }

            return result;
        }

        /// <summary>
        /// 创建日志文件主函数（一般不直接调用）
        /// </summary>
        /// <param name="filepath">文件路径＋文件名</param>
        /// <param name="strbody">文件内容</param>
        /// <returns></returns>
        public bool WriteLog(string filepath, string name, string strbody)
        {

            StreamWriter my_writer = null;

            try
            {
                if (Directory.Exists(filepath) == false)
                {
                    Directory.CreateDirectory(filepath);
                }
                //如果文件存在，则自动追加方式写
                my_writer = new StreamWriter(filepath + name, true, System.Text.Encoding.Default);
                my_writer.Write(strbody);
                my_writer.Flush();
                return true;

            }
            catch
            {
                return false;
            }
            finally
            {
                if (my_writer != null)
                    my_writer.Close();
            }
        }
        #endregion
    }
}

